██████╗ ██████╗ █████╗ ████████╗ ██████╗ ██████╗ ██╗ ███████╗
██╔══██╗██╔══██╗██╔══██╗╚══██╔══╝██╔═══██╗██╔═══██╗██║ ██╔════╝
██║ ██║██████╔╝███████║ ██║ ██║ ██║██║ ██║██║ ███████╗
██║ ██║██╔══██╗██╔══██║ ██║ ██║ ██║██║ ██║██║ ╚════██║
██████╔╝██████╔╝██║ ██║ ██║ ╚██████╔╝╚██████╔╝███████╗███████║
╚═════╝ ╚═════╝ ╚═╝ ╚═╝ ╚═╝ ╚═════╝ ╚═════╝ ╚══════╝╚══════╝
██████╗ ███████╗ ██████╗██╗██████╗ ███████╗ ██╗ ██╗ ██████╗ ███████╗
██╔══██╗██╔════╝██╔════╝██║██╔══██╗██╔════╝ ████████╗██╔═████╗╚════██║
██████╔╝█████╗ ██║ ██║██████╔╝█████╗ ╚██╔═██╔╝██║██╔██║ ██╔╝
██╔══██╗██╔══╝ ██║ ██║██╔═══╝ ██╔══╝ ████████╗████╔╝██║ ██╔╝
██║ ██║███████╗╚██████╗██║██║ ███████╗ ╚██╔═██╔╝╚██████╔╝ ██║
╚═╝ ╚═╝╚══════╝ ╚═════╝╚═╝╚═╝ ╚══════╝ ╚═╝ ╚═╝ ╚═════╝ ╚═╝
</pre>
Note: I have blogged about it: Using dbatools to verify your SQL Server instances version compliance
Set variables
In [ ]:
$dbatools1 = "localhost,1433"
$dbatools2 = "localhost,14333"
$secureString = ConvertTo-SecureString "dbatools.IO" -AsPlainText -Force
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "sqladmin", $secureString
$database = "dbatools"
Check if instance is on lastest available build
In [ ]:
Test-DbaBuild -SqlInstance $dbatools1 -SqlCredential $cred -Latest
In [ ]:
Test-DbaBuild -SqlInstance $dbatools1 -SqlCredential $cred -MaxBehind 1CU
Example using the Meltdown/Spectre versions
In [ ]:
#Meltdown/Spectre check:
$mapping = @{
'2008' = '10.0.6556'
'2008R2' = '10.50.6560'
'2012' = '11.0.7462'
'2014' = '12.0.5571'
'2016' = '13.0.4466'
'2017' = '14.0.3015'
}
foreach($inst in @($dbatools1, $dbatools2)) {
$ref = Get-DbaBuildReference -SqlInstance $inst -SqlCredential $cred
Test-DbaBuild -SqlInstance $inst -SqlCredential $cred -MinimumBuild $mapping[$ref.NameLevel]
}
In [ ]:
# Excel magic
$excelFilePath = "D:\Presentations\PASS Marathon Portuguese 2020\Test-Compliance\Compliance_$((Get-Date).ToFileTime()).xlsx"
$results = Test-DbaBuild -SqlInstance $dbatools1,$dbatools2 -SqlCredential $cred -Latest
$results | Export-Excel -Path $excelFilePath -TableName "data" -TableStyle Medium10 -Show